﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Reflection;
using Microsoft.EntityFrameworkCore;
using Abp.Domain.Uow;
using System.Data.SqlClient;
using Abp.Runtime.Caching;

namespace FwProject.PublicService.SqlExtensions
{
    public static class EFSqlQueryExtensions
    {
        [UnitOfWork(isTransactional: false)]
        public static IList<T> DynamicListFromSql<T>(this DbContext db, string Sql, Dictionary<string, object> Params) where T: class ,new()
        {
            IList<T> list;
            SqlConnection Con = new SqlConnection(db.Database.GetDbConnection().ConnectionString);
         
            using (var cmd = Con.CreateCommand())
            {
                cmd.CommandText = Sql;
                if (cmd.Connection.State != ConnectionState.Open) { cmd.Connection.Open(); }
                if (Params != null)
                {
                    foreach (KeyValuePair<string, object> p in Params)
                    {
                        DbParameter dbParameter = cmd.CreateParameter();
                        dbParameter.ParameterName = p.Key;
                        dbParameter.Value = p.Value;
                        cmd.Parameters.Add(dbParameter);
                    }
                }
                list = new List<T>();
                using ( var tr= Con.BeginTransaction()) {
                    cmd.Transaction = tr;
                    using (var dataReader = cmd.ExecuteReader())
                    {
                        while (dataReader.Read())
                        {
                            T t = new T();
                            PropertyInfo[] propertys = t.GetType().GetProperties();
                            foreach (PropertyInfo pi in propertys)
                            {
                               var attr=  pi.GetCustomAttribute<FwProject.Comm.SqlMapAttribute>();
                                if (attr != null && attr.IsNotMap == true)
                                {
                                    continue;
                                }
                                if (!pi.CanWrite) { continue; }
                                try
                                {
                                    var value = dataReader[pi.Name];
                                    if (value != DBNull.Value)
                                    {
                                        pi.SetValue(t, value, null);
                                    }
                                }
                                catch (Exception exe)
                                {
                                    System.Console.WriteLine($"EFSqlQueryExtensions:{exe.ToString()}");
                                }
                              
                            }
                            list.Add(t);
                        }
                    }
                    tr.Commit();
                }
                Con.Close();
                return list;
            }
        }
    }
}
